/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     2015/5/13 11:27:30                           */
/*==============================================================*/


if exists (select 1
            from  sysobjects
           where  id = object_id('ApproveTasks')
            and   type = 'U')
   drop table ApproveTasks
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AttendanceSort')
            and   type = 'U')
   drop table AttendanceSort
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AttendanceSync')
            and   type = 'U')
   drop table AttendanceSync
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Categories')
            and   type = 'U')
   drop table Categories
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DaysOff')
            and   type = 'U')
   drop table DaysOff
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Department')
            and   type = 'U')
   drop table Department
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Employee')
            and   type = 'U')
   drop table Employee
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EmployeeCenter')
            and   type = 'U')
   drop table EmployeeCenter
go

if exists (select 1
            from  sysobjects
           where  id = object_id('FlowsLogs')
            and   type = 'U')
   drop table FlowsLogs
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ForgotCard')
            and   type = 'U')
   drop table ForgotCard
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SystemSettings')
            and   type = 'U')
   drop table SystemSettings
go

if exists (select 1
            from  sysobjects
           where  id = object_id('VacationAndWeekdays')
            and   type = 'U')
   drop table VacationAndWeekdays
go

if exists (select 1
            from  sysobjects
           where  id = object_id('WorkflowDefine')
            and   type = 'U')
   drop table WorkflowDefine
go

if exists (select 1
            from  sysobjects
           where  id = object_id('overtime')
            and   type = 'U')
   drop table overtime
go

/*==============================================================*/
/* Table: ApproveTasks                                          */
/*==============================================================*/
create table FP_TB_ApproveTasks (
   ID                   int                  identity,
   ModuleType           int                  null,
   Link                 varchar(200)         null,
   InternalId           int                  null,
   Assignee             int                  null,
   AssigneeName         nvarchar(50)         null,
   Status               int                  null,
   OwnerId              int                  null,
   TaskDateTime         int                  null,
   TaskDoneTime         datetime             null,
   constraint PK_APPROVETASKS primary key (ID)
)
go

/*==============================================================*/
/* Table: AttendanceSort                                        */
/*==============================================================*/
create table FP_TB_AttendanceSort (
   ID                   int                  identity,
   WorkBenginTime       datetime             null,
   WorkEndTime          datetime             null,
   Year                 int                  null,
   Month                int                  null,
   EmployeeNumber       varchar(50)          null,
   Department           nvarchar(50)         null,
   EmployeeName         nvarchar(50)         null,
   OvertimeLength       float(5)             null,
   WorkDate             datetime             null,
   SortTime             datetime             null,
   DepartmentID         int                  null,
   EmployeeID           int                  null,
   constraint PK_ATTENDANCESORT primary key (ID)
)
go

/*==============================================================*/
/* Table: AttendanceSync                                        */
/*==============================================================*/
create table FP_TB_AttendanceSync (
   ID                   int                   identity,
   EmployeeNumber       varchar(50)          null,
   Department           nvarchar(50)         null,
   TouchTime            datetime             null,
   EmployeeName         nvarchar(50)         null,
   constraint PK_ATTENDANCESYNC primary key (ID)
)
go

/*==============================================================*/
/* Table: Categories                                            */
/*==============================================================*/
create table FP_TB_Categories (
   ID                   int                   identity,
   ModuleName           nvarchar(50)         null,
   DisplayName          nvarchar(50)         null,
   Value                varchar(50)          null,
   constraint PK_CATEGORIES primary key (ID)
)
go

/*==============================================================*/
/* Table: DaysOff                                               */
/*==============================================================*/
create table FP_TB_DaysOff (
   ID                   int                  identity,
   BeginTime            datetime             null,
   EndTime              datetime             null,
   TimeLength           float(5)             null,
   OwnerId              int                  null,
   VacateType           int                  null,
   Reason               nvarchar(500)        null,
   SubmitTime           datetime             null,
   Status               int                  null,
   Department           nvarchar(50)         null,
   VacateOrDaysOff      int                  null,
   DepartmentID         int                  null,
   constraint PK_DAYSOFF primary key (ID)
)
go

/*==============================================================*/
/* Table: Department                                            */
/*==============================================================*/
create table FP_TB_Department (
   ID                   int                  identity,
   DepartmentName       varchar(50)          null,
   DepartmentLeaderID   int                  null,
   DepartmentLeaderName nvarchar(50)         null,
   IsBoss               int                  null
)
go

/*==============================================================*/
/* Table: Employee                                              */
/*==============================================================*/
create table FP_TB_Employee (
   ID                   int                  identity,
   EmployeeNumber       varchar(50)          null,
   Email                varchar(50)          null,
   ChineseName          nvarchar(50)         null,
   EnglishName          varchar(50)          null,
   MobilePhone          varchar(50)          null,
   DepartmentName       nvarchar(50)         null,
   JobTitle             varchar(50)          null,
   JoinDate             datetime             null,
   Status               int                  null,
   Sex                  int                  null,
   Age                  int                  null,
   IsMarried            int                  null,
   DepartmentID         int                  null,
   constraint PK_EMPLOYEE primary key (ID)
)
go

/*==============================================================*/
/* Table: EmployeeCenter                                        */
/*==============================================================*/
create table FP_TB_EmployeeCenter (
   ID                   int                   identity,
   EmployeeID           int                  null,
   DaysOff              float(5)             null,
   AnnualVacationDays   int                  null,
   SickDays             int                  null,
   ForgetCard           int                  null,
   Lated                int                  null,
   constraint PK_EMPLOYEECENTER primary key (ID)
)
go

/*==============================================================*/
/* Table: FlowsLogs                                             */
/*==============================================================*/
create table FP_TB_FlowsLogs (
   ID                   int                 identity,
   TaskID               int                  null,
   LogTime              datetime             null,
   ApproverID           int                  null,
   ApproveOrReject      int                  null,
   Comments             nvarchar(300)        null,
   ApproverName         nvarchar(50)         null,
   constraint PK_FLOWSLOGS primary key (ID)
)
go

/*==============================================================*/
/* Table: ForgotCard                                            */
/*==============================================================*/
create table FP_TB_ForgotCard (
   ID                   int                  identity,
   OwnerName            nvarchar(50)         null,
   OwnerId              int                  null,
   JobTitle             varchar(50)          null,
   ForgetTime           datetime             null,
   Reason               nvarchar(500)        null,
   Status               int                  null,
   DepartmentID         int                  null,
   SubmitTime           datetime             null,
   DepartmentName       nvarchar(50)         null,
   constraint PK_FORGOTCARD primary key (ID)
)
go

/*==============================================================*/
/* Table: SystemSettings                                        */
/*==============================================================*/
create table FP_TB_SystemSettings (
   ID                   int                  identity,
   WorkBeginTime        datetime             null,
   WorkEndTime          datetime             null,
   OverTimeBeginTime    datetime             null,
   SickDays             int                  null,
   AnnualVacationDays   int                  null,
   constraint PK_SYSTEMSETTINGS primary key (ID)
)
go

/*==============================================================*/
/* Table: VacationAndWeekdays                                   */
/*==============================================================*/
create table FP_TB_VacationAndWeekdays (
   ID                   int                  identity,
   VacationType         int                  null,
   VacationDate         datetime             null,
   VacationName         nvarchar(50)         null,
   constraint PK_VACATIONANDWEEKDAYS primary key (ID)
)
go

/*==============================================================*/
/* Table: WorkflowDefine                                        */
/*==============================================================*/
create table FP_TB_WorkFlowDefination (
   ID                   int                  identity,
   ModuleID             int                  null,
   FlowOrder            int                  null,
   DepartmentID         int                  null,
   DepartmentName       varchar(50)          null,
   WorkFlowName         varchar(50)          null,
   constraint PK_WORKFLOWDEFINATION primary key (ID)
)
go

/*==============================================================*/
/* Table: overtime                                              */
/*==============================================================*/
create table FP_TB_Overtime (
   ID                   int                  identity,
   BeginTime            datetime             null,
   EndTime              datetime             null,
   TimeLength           float(5)             null,
   OwnerId              int                  null,
   OwnerName            nvarchar(50)         null,
   VacationType         int                  null,
   Reason               nvarchar(500)        null,
   SubmitTime           datetime             null,
   Status               int                  null,
   DepartmentName       nvarchar(50)         null,
   JobTitle             varchar(50)          null,
   CreatorUserId        int                  null,
   CreateDate           datetime             null,
   CreateUserName       nvarchar(50)         null,
   DepartmentID         int                  null,
   constraint PK_OVERTIME primary key (ID)
)
go

